package sparkcore.day7.lesson04

import org.apache.spark.sql.SQLContext
import org.apache.spark.{SparkConf, SparkContext}

/**
  * Created by Administrator on 2018/5/3.
  */
object ApacheLogBySQL {
  def main(args: Array[String]): Unit = {
    val conf = new SparkConf()
    conf.setMaster("local")
    conf.setAppName(s"${this.getClass.getSimpleName}")
    val sc = new SparkContext(conf)
    val sqlContext = new SQLContext(sc)
    /**
      *思路：
      * 1）首先读取.txt 文件，=》RDD
      * 2） RDD =》 DataFrame
      *     反射
      *     编程
      * 3)  SQL
      */
   import  sqlContext.implicits._
    val df = sc.textFile("D:\\1711班\\第十三天\\资料\\log.txt")
      .map(line => ApacheLog.parseLog(line)).toDF()
    df.createOrReplaceTempView("log")
    
    
    /**
      * 需求一：
      The average, min, and max content size of responses returned from the server.
      */
    val sql1=
      """
         select
               avg(contentSize),
               min(contentSize),
               max(contentSize)
         from
               log

      """

    sqlContext.sql(sql1).show()
    /**
      * 需求二：
      A count of response code's returned.
      */
    val sql2=
      """
         select
               resposeCode,count(*)
         from
              log
         group by
              resposeCode
      """

    sqlContext.sql(sql2).show()

    /**
      * 需求三：
      All IPAddresses that have accessed this server more than 100 times.
     哪些IP地址访问我们的网站超过N次
      */

    val sql3=
      """
        select
              ipAddress,
              count(*) as count
         from
              log
         group by
              ipAddress
         having
              count > 2
      """

    sqlContext.sql(sql3).show()


    /**
      * 需求四：
      The top endpoints requested by count.  TopN
      找出被访问次数最多的地址的前三个
      */

    var sql4=
      """
        select
              endPoint,
              count(*) as total
         from
              log
         group by
              endPoint
         order by
              total desc
         limit 3
      """

    sqlContext.sql(sql4).show()


  }

}
